﻿#region

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

#endregion

namespace DataAccessLayer
{
    public static class PickAndDropBookingDAL
    {
        private static readonly string ConnectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        public static void NewPickAndDropBooking(PickAndDropBooking booking)
        {
            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))
            {
                con.Open();
                var cmd = new SqlCommand("spNewPickAndDropBooking", con) {CommandType = CommandType.StoredProcedure};
                cmd.Parameters.Add(new SqlParameter("@CustomerId", booking.CustomerId) {SqlDbType = SqlDbType.Int});
                cmd.Parameters.Add(new SqlParameter("@Type", booking.Type));
                cmd.Parameters.Add(new SqlParameter("@OriginDateTime", booking.OriginDateTime)
                {
                    SqlDbType = SqlDbType.DateTime
                });
                cmd.Parameters.Add(new SqlParameter("@HomeOrNumber", booking.OriginLocation.HomeNumberOrName));
                cmd.Parameters.Add(new SqlParameter("@Street", booking.OriginLocation.Street));
                cmd.Parameters.Add(new SqlParameter("@City", booking.OriginLocation.City));
                cmd.Parameters.Add(new SqlParameter("@DistrictId", booking.OriginLocation.DistrictId)
                {
                    SqlDbType = SqlDbType.Int
                });
                cmd.Parameters.Add(new SqlParameter("@Midpoint", booking.MidPoint));
                cmd.Parameters.Add(new SqlParameter("@NumberOfCars", booking.VehicleRequirement.NumberOfCars)
                {
                    SqlDbType = SqlDbType.Int
                });
                cmd.Parameters.Add(new SqlParameter("@NumberOfVans", booking.VehicleRequirement.NumberOfVans)
                {
                    SqlDbType = SqlDbType.Int
                });
                cmd.ExecuteNonQuery();
            }
        }

        public static List<PickAndDropBooking> GetAllPickAndDropBookings(int customerId)
        {
            var bookings = new List<PickAndDropBooking>();
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))
            {
                var command = new SqlCommand("spGetAllPickAndDropBookingByCustomerId", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };
                command.Parameters.Add(new SqlParameter("@CustomerId", customerId) {SqlDbType = SqlDbType.Int});
                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var booking = new PickAndDropBooking
                        {
                            BookingId = (int) reader["BookingId"],
                            MidPoint = reader["MidPoint"].ToString(),
                            BookedDateTime = (DateTime) reader["BookedDateTime"],
                            OriginDateTime = (DateTime) reader["OriginDateTime"],
                            Status = reader["Status"].ToString()
                        };
                        if (!reader["ReturnedDateTime"].Equals(DBNull.Value))
                        {
                            booking.ReturnedDateTime = (DateTime) reader["ReturnedDateTime"];
                        }
                        if (!reader["SupervisorId"].Equals(DBNull.Value))
                        {
                            booking.SupervisorId = (int) reader["SupervisorId"];
                        }
                        if (!reader["TravelledDistance"].Equals(DBNull.Value))
                        {
                            booking.TravelledDistance = (int) reader["TravelledDistance"];
                        }
                        if (!reader["DeliveredDateTime"].Equals(DBNull.Value))
                        {
                            booking.DeliveredDateTime = (DateTime) reader["DeliveredDateTime"];
                        }
                        CurrentLocation location = new CurrentLocation
                        {
                            HomeNumberOrName = reader["HomeNumberOrName"].ToString(),
                            Street = reader["Street"].ToString(),
                            City = reader["City"].ToString(),
                            DistrictId = (byte) reader["DistrictId"]
                        };
                        BookingVehicleRequirement req = new BookingVehicleRequirement
                        {
                            NumberOfCars = Convert.ToInt32(reader["NumberOfCars"]),
                            NumberOfVans = Convert.ToInt32(reader["NumberOfVans"])
                        };
                        booking.OriginLocation = location;
                        booking.VehicleRequirement = req;
                        bookings.Add(booking);
                    }
                    return bookings;
                }
            }
        }

        public static void UpdatePickAndDropBooking(PickAndDropBooking booking)
        {
            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))
            {
                con.Open();
                var cmd = new SqlCommand("spUpdatePickAndDropBooking", con) {CommandType = CommandType.StoredProcedure};
                cmd.Parameters.Add(new SqlParameter("@BookingId", booking.BookingId) {SqlDbType = SqlDbType.Int});
                cmd.Parameters.Add(new SqlParameter("@OriginDateTime", booking.OriginDateTime)
                {
                    SqlDbType = SqlDbType.DateTime
                });
                cmd.Parameters.Add(new SqlParameter("@HomeOrNumber", booking.OriginLocation.HomeNumberOrName));
                cmd.Parameters.Add(new SqlParameter("@Street", booking.OriginLocation.Street));
                cmd.Parameters.Add(new SqlParameter("@City", booking.OriginLocation.City));
                cmd.Parameters.Add(new SqlParameter("@DistrictId", booking.OriginLocation.DistrictId)
                {
                    SqlDbType = SqlDbType.Int
                });
                cmd.Parameters.Add(new SqlParameter("@Midpoint", booking.MidPoint));
                cmd.Parameters.Add(new SqlParameter("@NumberOfCars", booking.VehicleRequirement.NumberOfCars)
                {
                    SqlDbType = SqlDbType.Int
                });
                cmd.Parameters.Add(new SqlParameter("@NumberOfVans", booking.VehicleRequirement.NumberOfVans)
                {
                    SqlDbType = SqlDbType.Int
                });
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Get all bookings by booking Id
        /// </summary>
        /// <param name="bookingId">Represent's the booking Id</param>
        /// <returns>Booking</returns>
        public static PickAndDropBooking GetBooking(int bookingId)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetPickAndDropBookingById", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@BookingId", bookingId);

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (!reader.Read()) return null;
                    PickAndDropBooking booking = new PickAndDropBooking
                    {
                        BookingId = Convert.ToInt32(reader["BookingId"]),
                        CustomerId = Convert.ToInt32(reader["CustomerId"]),
                        Type = reader["Type"].ToString(),
                        BookedDateTime = Convert.ToDateTime(reader["BookedDateTime"]),
                        OriginDateTime = Convert.ToDateTime(reader["OriginDateTime"]),
                        Status = reader["Status"].ToString(),
                        MidPoint = reader["MidPoint"].ToString(),
                        VehicleDrivers = Booking_Vehicle_DriverDAL.GetVehicleAndDriverDetailsByBookingId(bookingId)
                    };

                    CurrentLocation currentLocation = new CurrentLocation
                    {
                        HomeNumberOrName = reader["HomeNumberOrName"].ToString(),
                        Street = reader["Street"].ToString(),
                        City = reader["City"].ToString(),
                        DistrictId = Convert.ToByte(reader["DistrictId"])
                    };

                    booking.OriginLocation = currentLocation;

                    BookingVehicleRequirement vehicleRequirement = new BookingVehicleRequirement
                    {
                        NumberOfCars = Convert.ToInt32(reader["NumberOfCars"]),
                        NumberOfVans = Convert.ToInt32(reader["NumberOfVans"])
                    };

                    booking.VehicleRequirement = vehicleRequirement;

                    //Following are possible nullable columns

                    if (reader["SupervisorId"] != DBNull.Value)
                    {
                        booking.SupervisorId = Convert.ToInt32(reader["SupervisorId"]);
                    }

                    if (reader["TravelledDistance"] != DBNull.Value)
                    {
                        booking.TravelledDistance = Convert.ToInt32(reader["TravelledDistance"]);
                    }

                    if (reader["DeliveredDateTime"] != DBNull.Value)
                    {
                        booking.DeliveredDateTime = Convert.ToDateTime(reader["DeliveredDateTime"]);
                    }

                    if (reader["ReturnedDateTime"] != DBNull.Value)
                    {
                        booking.ReturnedDateTime = Convert.ToDateTime(reader["ReturnedDateTime"]);
                    }

                    if (reader["ApproximateEndDateTime"] != DBNull.Value)
                    {
                        booking.ApproximateEndDateTime = Convert.ToDateTime(reader["ApproximateEndDateTime"]);
                    }

                    return booking;
                }
            }
        }

        /// <summary>
        ///     Set the vehicle returned date and time
        /// </summary>
        /// <param name="bookingId">Represent's the booking Id</param>
        /// <param name="returneDateTime">Represent's the vehicle returned date and time</param>
        public static void SetReturnedDate(int bookingId, DateTime returneDateTime)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spSetReturnedDateTime", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@BookingId", bookingId);
                command.Parameters.AddWithValue("@ReturnedDateTime", returneDateTime);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }
}